import xlrd

wb = xlrd.open_workbook(filename="D:/pythonProject/excel/baidu-员工的人员信息.xls")
st = wb.sheet_by_index(0)

#统计总人数
def getNumStaff():
    numStaff = st.nrows - 1
    print(numStaff)

#获取手机号前3位或前4位
def getNumHead(num):
    num = str(num)
    a = num[:3]
    a = int(a)
    if a == 170:
        a = num[:4]
        a = int(a)
        return a
    else:
        return a

#统计电话号运营商
def deteOrange():
    yd = [134, 135, 136, 137, 138, 139, 147, 150, 151, 152, 157, 158, 159, 178, 182, 183, 184, 187, 188, 1703, 1705, 1706]
    lt = [130, 131, 132, 145, 155, 156, 175, 176, 185, 186, 1704, 1707, 1708, 1709, 171]
    dx = [133, 149, 153, 173, 177, 180, 181, 189, 1700, 1701, 1702]
    ydUser = 0
    ltUser = 0
    dxUser = 0
    atherUser = 0
    telNub = st.col_values(5)[1:]
    for i in telNub:
        a = getNumHead(i)
        if a in yd:
            ydUser = ydUser + 1
        elif a in lt:
            ltUser = ltUser + 1
        elif a in dx:
            dxUser = dxUser + 1
        else:
            atherUser = atherUser + 1
    print("移动用户人数：%d  联通用户人数：%d  电信用户人数：%d  其他用户人数：%d" % (ydUser, ltUser, dxUser, atherUser))
    print("移动用户占比为：{:.2f}%".format((ydUser/65534)*100))
    print("联通用户占比为：{:.2f}%".format((ltUser/65534)*100))
    print("电信用户占比为：{:.2f}%".format((dxUser/65534)*100))

#统计公司男女人数
def deteSex():
    sex = st.col_values(8)[1:]
    male = 0
    female = 0
    for i in sex:
        if i == '男':
            male = male + 1
        else:
            female = female + 1
    print("男性人数为：%d  女性人数为：%d" % (male, female))

#老员工人数
def oldStaff():
    age = st.col_values(7)[1:]
    oldStaff = 0
    for i in age:
        if i >= 45:
            oldStaff = oldStaff + 1
    print("老员工人数为%d" % (oldStaff))

#薪资高于8000和低于3000
def deteSalary():
    salary =  st.col_values(11)[1:]
    rich = 0
    poor = 0
    for i in salary:
        if i >= 8000:
            rich = rich + 1
        elif i < 3000:
            poor = poor + 1
    print("薪资超过8000元人数为：%d  薪资低于3000元人数为：%d" % (rich, poor))

#传媒公司人员
def media():
    cop = st.col_values(13)[1:]
    data = 0
    for i in cop:
        if "传媒" in i:
            data = data + 1
    print("去传媒公司的员工数%d" % (data))

#高危地区人员
def dagAreaPer():
    area = st.col_values(9)[1:]
    dagArea = ["黑龙","北京","福建","四川"]
    data = 0
    for i in area:
        a = i[:2]
        if a in dagArea:
            data = data + 1
    print('高危地区人数为%d' % (data))

while True:
    print(f'请选择功能:\n1统计总人数\n2统计电话运营商\n3统计男女人数\n4统计老龄员工\n5统计薪资分布\n6统计传媒公司人员\n7统计高危地区人数\n0退出')
    choose = int(input('输入选择'))
    if choose == 1:
        print('-----------------------------------------------------')
        getNumStaff()
        print('-----------------------------------------------------')
    elif choose == 2:
        print('-----------------------------------------------------')
        deteOrange()
        print('-----------------------------------------------------')
    elif choose == 3:
        print('-----------------------------------------------------')
        deteSex()
        print('-----------------------------------------------------')
    elif choose == 4:
        print('-----------------------------------------------------')
        oldStaff()
        print('-----------------------------------------------------')
    elif choose == 5:
        print('-----------------------------------------------------')
        deteSalary()
        print('-----------------------------------------------------')
    elif choose == 6:
        print('-----------------------------------------------------')
        media()
        print('-----------------------------------------------------')
    elif choose == 7:
        print('-----------------------------------------------------')
        dagAreaPer()
        print('-----------------------------------------------------')
    elif choose == 0:
        print('-----------------------------------------------------')
        print('您已退出系统')
        print('-----------------------------------------------------')
        break
    else:
        print('-----------------------------------------------------')
        print('无法查询')
        print('-----------------------------------------------------')


